<?php
/**
 * Model Database Connection Test
 * Verify all updated models can connect to their respective tables
 * 
 * Usage: php test_model_connections.php
 */

// Load environment variables from .env file
$envFile = __DIR__ . '/../../.env';
if (file_exists($envFile)) {
    $lines = file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        if (strpos($line, '=') !== false && strpos($line, '#') !== 0) {
            list($key, $value) = explode('=', $line, 2);
            $_ENV[trim($key)] = trim($value);
        }
    }
}

echo "🧪 Testing Model Database Connections...\n\n";

// Database configuration
try {
    $host = $_ENV['DB_HOST'] ?? '127.0.0.1';
    $port = $_ENV['DB_PORT'] ?? 3306;
    $dbname = $_ENV['DB_DATABASE'] ?? 'XappTool';
    $username = $_ENV['DB_USERNAME'] ?? 'root';
    $password = $_ENV['DB_PASSWORD'] ?? '';
    
    echo "Connecting to database: $host:$port/$dbname\n";
    echo "Using username: $username\n\n";
    
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "✅ Database connection established successfully.\n\n";
    
} catch (PDOException $e) {
    echo "❌ Database connection failed: " . $e->getMessage() . "\n";
    exit(1);
}

// Models to test
$models = [
    'app\common\model\Device' => 'gf_device',
    'app\common\model\ChannelStats' => 'gf_channel_stats',
    'app\common\model\AttributionEvent' => 'gf_attribution_event',
    'app\common\model\AttributionInstall' => 'gf_attribution_install',
    'app\common\model\AttributionTrack' => 'gf_attribution_track',
];

$successCount = 0;
$failedCount = 0;
$errors = [];

foreach ($models as $modelClass => $expectedTable) {
    echo "Testing: $modelClass\n";
    echo "Expected table: $expectedTable\n";
    
    try {
        // Check if class exists
        if (!class_exists($modelClass)) {
            echo "  ❌ Class not found: $modelClass\n";
            $failedCount++;
            $errors[] = "$modelClass: Class not found";
            continue;
        }
        
        // Get table name from model
        try {
            $reflection = new ReflectionClass($modelClass);
            $property = $reflection->getProperty('table');
            $property->setAccessible(true);
            
            // Create instance to get table name
            $instance = $reflection->newInstance();
            $actualTable = $property->getValue($instance);
            
            echo "  Actual table: $actualTable\n";
            
            if ($actualTable === $expectedTable) {
                echo "  ✅ Table name matches expected value\n";
            } else {
                echo "  ⚠️  Table name mismatch: expected $expectedTable, got $actualTable\n";
            }
        } catch (Exception $e) {
            echo "  ⚠️  Could not get table name: " . $e->getMessage() . "\n";
        }
        
        // Check if table exists in database
        try {
            $stmt = $pdo->prepare("SHOW TABLES LIKE ?");
            $stmt->execute([$expectedTable]);
            $tableExists = $stmt->fetch();
            
            if ($tableExists) {
                echo "  ✅ Table exists in database\n";
                
                // Try to query the table
                try {
                    $countStmt = $pdo->prepare("SELECT COUNT(*) as count FROM `$expectedTable`");
                    $countStmt->execute();
                    $result = $countStmt->fetch(PDO::FETCH_ASSOC);
                    echo "  📊 Row count: " . $result['count'] . "\n";
                    
                    if ($result['count'] > 0) {
                        echo "  ✅ Table contains data\n";
                    } else {
                        echo "  ℹ️  Table is empty (this is OK for new tables)\n";
                    }
                    
                    $successCount++;
                } catch (PDOException $e) {
                    echo "  ❌ Error querying table: " . $e->getMessage() . "\n";
                    $failedCount++;
                    $errors[] = "$modelClass: Error querying table - " . $e->getMessage();
                }
            } else {
                echo "  ❌ Table does not exist in database\n";
                $failedCount++;
                $errors[] = "$modelClass: Table $expectedTable does not exist";
            }
        } catch (PDOException $e) {
            echo "  ❌ Error checking table existence: " . $e->getMessage() . "\n";
            $failedCount++;
            $errors[] = "$modelClass: Error checking table - " . $e->getMessage();
        }
        
    } catch (Exception $e) {
        echo "  ❌ Unexpected error: " . $e->getMessage() . "\n";
        $failedCount++;
        $errors[] = "$modelClass: Unexpected error - " . $e->getMessage();
    }
    
    echo "\n";
}

echo "📊 TEST RESULTS:\n";
echo "==================================================\n";
echo "Total models tested: " . count($models) . "\n";
echo "✅ Successful: $successCount\n";
echo "❌ Failed: $failedCount\n";
echo "Success rate: " . round(($successCount / count($models)) * 100, 2) . "%\n\n";

if ($failedCount > 0) {
    echo "❌ ERRORS ENCOUNTERED:\n";
    echo "==================================================\n";
    foreach ($errors as $error) {
        echo "- $error\n";
    }
    echo "\n";
}

if ($successCount === count($models)) {
    echo "🎉 ALL MODELS TESTED SUCCESSFULLY!\n";
    echo "✅ All table mappings are correct and tables exist in the database.\n";
} else {
    echo "⚠️  Some issues were found. Please review the errors above.\n";
}

echo "\n🔍 ADDITIONAL VERIFICATION:\n";
echo "==================================================\n";

// List all tables in database
try {
    $stmt = $pdo->query("SHOW TABLES");
    $allTables = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $gfTables = array_filter($allTables, function($table) {
        return strpos($table, 'gf_') === 0;
    });
    
    echo "Total tables in database: " . count($allTables) . "\n";
    echo "Tables with gf_ prefix: " . count($gfTables) . " ✅\n";
    echo "Tables without gf_ prefix: " . (count($allTables) - count($gfTables)) . "\n\n";
    
    if (count($allTables) === count($gfTables)) {
        echo "🎉 PERFECT: All tables use gf_ prefix!\n";
    } else {
        echo "⚠️  Some tables still don't use gf_ prefix:\n";
        foreach ($allTables as $table) {
            if (strpos($table, 'gf_') !== 0) {
                echo "  - $table\n";
            }
        }
    }
} catch (PDOException $e) {
    echo "❌ Error listing tables: " . $e->getMessage() . "\n";
}

echo "\n🏁 Model connection test completed!\n";

?>